package com.jxl.demo.control;

import com.jxl.demo.pojo.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@CrossOrigin(origins = "*")
public class ScreenController {
    @Autowired(required = false) //初始化对象
    private JdbcTemplate jdbc;


    //查询总人数
    //http://192.168.137.139:8080/totalPerson
    @GetMapping("/totalPerson")
    public Result totalPerson(){
        Result result = new Result();
        result.setCode(200);
        result.setResult(jdbc.queryForObject("SELECT count(id) from userinfo",Integer.class));
        System.out.println("查询总人数成功");
        return result;
    }

    //查询各地区人数
    //http://192.168.137.139:8080/queryAddress
    @GetMapping("/queryAddress")
    public Result queryAddress(){
        Result result = new Result();
        try {
            List<AddressCount> addressCounts = jdbc.query("SELECT address, COUNT(address) AS counts FROM userinfo GROUP BY address",
                    new BeanPropertyRowMapper<>(AddressCount.class));
            result.setCode(200);
            result.setResult(addressCounts);
            System.out.println("查询各地区人数成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setResult("其他错误");
        }
        return result;
    }

    //查询男女人数
    //http://192.168.137.139:8080/genderCounts
    @GetMapping("/genderCounts")
    public Result genderCounts(){
        Result result = new Result();
        try {
            List<GenderCount> genderCounts = jdbc.query("SELECT gender, COUNT(gender) AS count FROM userinfo GROUP BY gender",
                    new BeanPropertyRowMapper<>(GenderCount.class));
            result.setCode(200);
            result.setResult(genderCounts);
            System.out.println("查询男女人数人数成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setResult("其他错误");
        }
        return result;
    }

    //查询各专业男女人数
    //http://192.168.137.139:8080/collegeGenderCounts
    @GetMapping("/collegeGenderCounts")
    public Result collegeGenderCounts(){
        Result result = new Result();
        try {
            List<CollegeGenderCount> collegeGenderCounts = jdbc.query(
                    "SELECT\n" +
                            "    college,\n" +
                            "    SUM(CASE WHEN gender = '男' THEN 1 ELSE 0 END) AS maleCount,\n" +
                            "    SUM(CASE WHEN gender = '女' THEN 1 ELSE 0 END) AS femaleCount\n" +
                            "FROM userinfo\n" +
                            "GROUP BY college;",
                    new BeanPropertyRowMapper<>(CollegeGenderCount.class)
            );
            result.setCode(200);
            result.setResult(collegeGenderCounts);
            System.out.println("查询各学院男女人数成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setResult("其他错误");
        }
        return result;
    }

    //查询各专业人数
    //http://192.168.137.139:8080/majorCounts
    @GetMapping("/majorCounts")
    public Result majorCounts(){
        Result result = new Result();
        try {
            List<MajorCount> majorCounts = jdbc.query("SELECT major, COUNT(major) AS count FROM userinfo GROUP BY major",
                    new BeanPropertyRowMapper<>(MajorCount.class));
            result.setCode(200);
            result.setResult(majorCounts);
            System.out.println("查询各专业人数成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setResult("其他错误");
        }
        return result;
    }

    //报到男生人数
    //http://192.168.137.139:8080/boyCounts
    @GetMapping("/boyCounts")
    public Result boyCounts(){
        Result result = new Result();
        try {
            result.setCode(200);
            result.setResult(jdbc.queryForObject("SELECT count(id) from userinfo where gender = '男'",Integer.class));
            System.out.println("查询男生人数成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setResult("其他错误");
        }
        return result;
    }

    //报到女生人数
    //http://192.168.137.139:8080/girlCounts
    @GetMapping("/girlCounts")
    public Result girlCounts(){
        Result result = new Result();
        try {
            result.setCode(200);
            result.setResult(jdbc.queryForObject("SELECT count(id) from userinfo where gender = '女'",Integer.class));
            System.out.println("查询女生人数成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setResult("其他错误");
        }
        return result;
    }

    //返回学生学号，姓名，年龄，专业
    //http://192.168.137.139:8080/showInfo
    @GetMapping("/showInfo")
    public Result showInfo(){
        Result result = new Result();
        try {
            List <UserInfo> userInfo = jdbc.query("select stuNumber, name, age, major from userinfo",
                    new BeanPropertyRowMapper<>(UserInfo.class));
            result.setCode(200);
            result.setResult(userInfo);
            System.out.println("查询学生学号，姓名，年龄，专业成功");
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setResult("其他错误");
        }
        return result;
    }


}
